package jt.classic.app.dms;

import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jt.classic.system.ISystemException;
import jt.classic.system.SimpleManager;
import jt.classic.system.object.IObject;
import jt.classic.system.object.ObjectLCManager;
import jt.classic.system.object.ObjectManager;
import jt.classic.system.workflow.WObject;
import jt.classic.system.workflow.WObjectRegister;

import org.limp.mine.HTMLTool;


public class DmsManager extends SimpleManager {
    public static final String DEFAULT_FOLDER_ID = "-1";

    public DmsManager(Connection conn) {
        super(conn);

        /*try {
            LicVerify lv = new LicVerify();
            lv.verify("dms100.lic", conn);
        } catch (Exception ex) {
            this.clean();
            throw new RuntimeException(ex);
        }*/
    }

    private void init() {
    }

    /**
     * 根据CREATORID得到文件夹对象
     *
     * @param id
     *            对象CREATORID
     * @return IObject
     * @throws ISystemException
     */
    public List<TreeNode> getFolderObjByCreatorId(String userid)
        throws ISystemException {
        String sql = "SELECT * FROM ISYS_OBJECT WHERE OBJ_KEY = 'FOLDER' and OBJ_CREATORID = '" +
            userid + "'";
        List<TreeNode> nodeList = new ArrayList<TreeNode>();
        List<Map> objList = this.trimmer.searchMultiData(sql);

        for (Map info : objList) {
            WObject object = null;

            // IObject obj = new IObjectImpl();
            TreeNode node = new TreeNode();

            if (info != null) {
                String key = (String) info.get("OBJ_KEY");
                String id = (String) info.get("ID");
                WObjectRegister register = WObjectRegister.getInstance();

                try {
                    object = register.findWObject(key);

                    // info.put("WOBJECT", object);
                    ObjectManager manager = new ObjectManager(this.conn);

                    Map content = manager.getObjectContentById(id, object);
                    List l = (ArrayList) content.get("DOC_HGBGODKQXB");
                    Map obj = (HashMap) l.get(0);
                    String pkid = (String) obj.get("PK_DOC_HGBGODKQXB");
                    node.setId(pkid);

                    String parentid = (String) obj.get("COL_PARENTFOLDERID");
                    node.setParentId(parentid);

                    String text = (String) obj.get("COL_FOLDERNAME");
                    node.setText(text);

                    // info.put("CONTENT", content);
                } catch (Exception ex) {
                    throw new ISystemException("can't find object['" + key +
                        "']", ex);
                }

                // obj.reload(info);
            } else {
                throw new ISystemException("can't find object by creatorid['" +
                    userid + "']");
            }

            nodeList.add(node);
        }

        return nodeList;
    }

    /**
     * 根据userid,parentid获取当前文件夹直接文件夹
     *
     * @param userid用户id
     * @param parentid当前节点id
     * @return 子节点集合
     * @throws ISystemException
     */
    public List<TreeNode> getChildrenFolder(String userid, String parentid) {
        String sql = "select doc_hgbgodkqxb.PK_DOC_HGBGODKQXB, doc_hgbgodkqxb.COL_FOLDERNAME, doc_hgbgodkqxb.COL_PARENTFOLDERID,isys_object.OBJ_SUMMARY FROM doc_hgbgodkqxb " +
            "Inner Join isys_object_relation ON doc_hgbgodkqxb.PK_DOC_HGBGODKQXB = isys_object_relation.OBJ_PKID " +
            "and isys_object_relation.OBJ_TABLE='DOC_HGBGODKQXB' Inner Join isys_object ON isys_object.ID = isys_object_relation.ID" +
            " where   isys_object.OBJ_CREATORID = '" + userid +
            "'   and doc_hgbgodkqxb.COL_PARENTFOLDERID = '" + parentid +
            "' order by isys_object.OBJ_KEY desc,OBJ_CREATETIME";
        List<TreeNode> nodeList = new ArrayList<TreeNode>();
        List<Map> objList = this.trimmer.searchMultiData(sql);

        for (Map info : objList) {
            TreeNode node = new TreeNode();

            if (info != null) {
                String pkid = (String) info.get("PK_DOC_HGBGODKQXB");
                node.setId(pkid);

                String pid = (String) info.get("COL_PARENTFOLDERID");
                node.setParentId(pid);

                String text = (String) info.get("COL_FOLDERNAME");
                node.setText(text);

                String summary = (String) info.get("OBJ_SUMMARY");
                node.setQtip(summary);

                if (hasChild(userid, pkid)) {
                    node.setLeaf(false);
                } else {
                    node.setLeaf(true);
                }

                // 生成全路径
                if ("-1".equals(pkid)) {
                    node.setFullpathId("");
                    node.setFullpath("");
                } else {
                    Map pfolder = getFolder(pid);
                    StringBuilder path = new StringBuilder();
                    StringBuilder ids = new StringBuilder();

                    while ((pfolder != null) &&
                            !"-1".equals(
                                (String) pfolder.get("PK_DOC_HGBGODKQXB"))) {
                        path.insert(0, (String) pfolder.get("COL_FOLDERNAME"));
                        path.insert(0, '/');
                        ids.insert(0, (String) pfolder.get("PK_DOC_HGBGODKQXB"));
                        ids.insert(0, '/');
                        pfolder = getFolder((String) pfolder.get(
                                    "COL_PARENTFOLDERID"));
                    }

                    node.setFullpathId(ids.toString());
                    node.setFullpath(path.toString());
                }

                nodeList.add(node);
            }
        }

        return nodeList;
    }

    public Map getFolder(String folderId) {
        String sql = "select doc_hgbgodkqxb.PK_DOC_HGBGODKQXB, doc_hgbgodkqxb.COL_FOLDERNAME, doc_hgbgodkqxb.COL_PARENTFOLDERID FROM doc_hgbgodkqxb " +
            " where    doc_hgbgodkqxb.PK_DOC_HGBGODKQXB = '" + folderId + "'";
        Map folder = this.trimmer.searchSingleData(sql);

        return folder;
    }

    /**
     * 根据userid和文件夹名称获取子目录
     *
     * @param userid
     * @param operator
     *            操作符
     * @param folderName
     *            文件夹名称
     * @return
     * @throws ISystemException
     */
    public List<Map> getChildrenFolderByName(String userid, String parentId,
        String operator, String folderName) {
        StringBuffer s = new StringBuffer(
                "select doc_hgbgodkqxb.PK_DOC_HGBGODKQXB, doc_hgbgodkqxb.COL_FOLDERNAME, doc_hgbgodkqxb.COL_PARENTFOLDERID FROM doc_hgbgodkqxb " +
                "Inner Join isys_object_relation ON doc_hgbgodkqxb.PK_DOC_HGBGODKQXB = isys_object_relation.OBJ_PKID " +
                "and isys_object_relation.OBJ_TABLE='DOC_HGBGODKQXB' Inner Join isys_object ON isys_object.ID = isys_object_relation.ID" +
                " where   isys_object.OBJ_CREATORID = '");
        s.append(userid + "'");
        s.append(" and  doc_hgbgodkqxb.COL_PARENTFOLDERID = '" + parentId +
            "' ");

        if ("=".equals(operator)) {
            s.append("  and doc_hgbgodkqxb.COL_FOLDERNAME " + operator + " '" +
                folderName +
                "' order by isys_object.OBJ_KEY desc,OBJ_CREATETIME");
        } else {
            s.append("  and doc_hgbgodkqxb.COL_FOLDERNAME " + operator + " '%" +
                folderName +
                "%' order by isys_object.OBJ_KEY desc,OBJ_CREATETIME");
        }

        List<Map> objList = this.trimmer.searchMultiData(s.toString());

        return objList;
    }

    /**
     * 获取当前文件夹下的所有直接文件夹及文件
     *
     * @param userid
     * @param folderid
     * @return
     * @throws ISystemException
     */
    public List<Map> getAllChildren(String userid, String isysObjId)
        throws ISystemException {
        ObjectLCManager manager = new ObjectLCManager(this.conn);

        IObject iobj = manager.getObjectById(isysObjId);

        String obj_key = iobj.ikey();

        if ("FILEINFO".equals(obj_key)) {
            return new ArrayList<Map>();
        }

        List l = (List) iobj.icontent().get("DOC_HGBGODKQXB");
        Map content = (Map) l.get(0);
        String folderid = (String) content.get("PK_DOC_HGBGODKQXB");
        StringBuffer where_file = new StringBuffer(" ");
        StringBuffer where_folder = new StringBuffer(" ");

        if (!"".equals(folderid) && !"null".equals(folderid) &&
                (folderid != null)) {
            where_file.append(" AND  doc_bvfabryjmq.COL_DIRECTORYID  = '" +
                folderid + "' ");
        }

        if (!"".equals(folderid) && !"null".equals(folderid) &&
                (folderid != null)) {
            where_folder.append(" AND   doc_hgbgodkqxb.COL_PARENTFOLDERID  = '" +
                folderid + "' ");
        }

        String sql_file_2 = "SELECT doc_bvfabryjmq.PK_DOC_BVFABRYJMQ as id," +
            "doc_bvfabryjmq.COL_FILENAME as filename," +
            "doc_bvfabryjmq.COL_FILESIZE as filesize," +
            "isys_object.OBJ_CREATORNAME as creatorname," +
            "isys_object.OBJ_CREATETIME as createtime," +
            "isys_object.OBJ_UPDATETIME as updatetime," +
            "doc_bvfabryjmq.COL_VERSION as version," +
            "doc_bvfabryjmq.COL_STATE as state," +
            "doc_bvfabryjmq.COL_FILEICON as fileicon," +
            "isys_object.OBJ_KEY as objkey," + "isys_object.ID as objid " +
            "FROM doc_bvfabryjmq Inner Join isys_object_relation ON doc_bvfabryjmq.PK_DOC_BVFABRYJMQ = isys_object_relation.OBJ_PKID " +
            "Inner Join isys_object ON isys_object_relation.ID = isys_object.ID " +
            " WHERE " + "isys_object.OBJ_KEY =  'FILEINFO' " +
            where_file.toString() + " and OBJ_CREATORID = " + userid;

        String sql_folder_2 = "SELECT doc_hgbgodkqxb.PK_DOC_HGBGODKQXB as id," +
            "doc_hgbgodkqxb.COL_FOLDERNAME as filename," +
            "doc_hgbgodkqxb.COL_FOLDERSIZE as filesize," +
            "isys_object.OBJ_CREATORNAME as creatorname," +
            "isys_object.OBJ_CREATETIME as createtime," + "'' as updatetime," +
            "'' as version," + "'' as state," +
            "doc_hgbgodkqxb.COL_FOLDERICON as fileicon," +
            "isys_object.OBJ_KEY as objkey," + "isys_object.ID as objid " +
            " FROM isys_object_relation Inner Join isys_object ON isys_object_relation.ID = isys_object.ID " +
            "Inner Join doc_hgbgodkqxb ON doc_hgbgodkqxb.PK_DOC_HGBGODKQXB = isys_object_relation.OBJ_PKID " +
            "WHERE isys_object.OBJ_KEY =  'FOLDER' " + where_folder.toString() +
            " and OBJ_CREATORID = " + userid;
        String sql = sql_file_2 + " union " + sql_folder_2 +
            " order by objkey desc,createtime";
        List<Map> resultList = this.trimmer.searchMultiData(sql);

        return resultList;
    }

    /**
     * 判断当前节点是否有子节点
     *
     * @param userid
     *            用户id
     * @param 节点id
     * @return 有子节点则返回true 反之false
     */
    public boolean hasChild(String userid, String nodeId) {
        String sql = "select doc_hgbgodkqxb.PK_DOC_HGBGODKQXB, doc_hgbgodkqxb.COL_FOLDERNAME, doc_hgbgodkqxb.COL_PARENTFOLDERID FROM doc_hgbgodkqxb " +
            "Inner Join isys_object_relation ON doc_hgbgodkqxb.PK_DOC_HGBGODKQXB = isys_object_relation.OBJ_PKID " +
            "and isys_object_relation.OBJ_TABLE='DOC_HGBGODKQXB' Inner Join isys_object ON isys_object.ID = isys_object_relation.ID" +
            " where   isys_object.OBJ_CREATORID = '" + userid +
            "'   and doc_hgbgodkqxb.COL_PARENTFOLDERID = '" + nodeId + "'";

        return this.trimmer.haveMoreData(sql);
    }

    /**
     * 获取附件表的主键
     *
     * @param fk_annex_id
     *            外键
     * @param objName
     *            表名
     * @return
     */
    public String getAnnexId(String fk_annex_id, String objName) {
        String xsystem_annex_id = "";
        String sql = "select xsystem_annex_id from xsystem_annex where fk_annex_id = '" +
            fk_annex_id + "' and annex_object = '" + objName + "'";
        Map m = this.trimmer.searchSingleData(sql);

        if (m != null) {
            xsystem_annex_id = (String) m.get("xsystem_annex_id");
        }

        return xsystem_annex_id;
    }

    /**
     * 判断目标文件夹下是否已有同名文件夹
     *
     * @param targetId
     * @param folderName
     * @return 如果有则返回true反之返回false
     */
    public boolean hasSameNameFolder(String targetId, String folderName) {
        boolean result = false;
        String sql = "select PK_DOC_HGBGODKQXB from doc_hgbgodkqxb where COL_PARENTFOLDERID = '" +
            targetId + "' and COL_FOLDERNAME = '" +
            HTMLTool.htmlEscape(folderName) + "'";
        Map m = this.trimmer.searchSingleData(sql);

        if (m != null) {
            result = true;
        }

        return result;
    }

    /**
     * 判断目标文件夹下是否已有同名文件
     *
     * @param targetId
     * @param folderName
     * @return 如果有则返回true反之返回false
     */
    public boolean hasSameNameFile(String targetId, String fileName) {
        boolean result = false;
        String sql = "select PK_DOC_BVFABRYJMQ from doc_bvfabryjmq where COL_DIRECTORYID = '" +
            targetId + "' and COL_FILENAME = '" +
            HTMLTool.htmlEscape(fileName) + "'";
        Map m = this.trimmer.searchSingleData(sql);

        if (m != null) {
            result = true;
        }

        return result;
    }

    /**
     * 在层级关联表中插入记录
     *
     * @param parentId
     *            父节点id
     * @param fid
     *            文件夹/文件id
     * @param type
     *            类型（FILEINFO/FOLDER）
     */
    public void insertRelation(String parentId, String fId, String type) {
        StringBuffer sql = new StringBuffer(
                "select * from doc_filerelation where fid = '");
        sql.append(parentId + "'");

        List<Map> objList = this.trimmer.searchMultiData(sql.toString());

        if (objList.size() == 0) {
            String sql_insert = "INSERT INTO DOC_FILERELATION (FID,PARENTID,LEVEL,TYPE) VALUES ('" +
                fId + "','" + parentId + "','0','" + type + "')";
            this.trimmer.execute(sql_insert);
        } else {
            StringBuffer sql_bath = new StringBuffer("");
            int level = 0;

            for (Map m : objList) {
                String pid = (String) m.get("PARENTID");
                level = Integer.parseInt((String) m.get("LEVEL"));
                // level++;
                sql_bath.append(
                    ";INSERT INTO DOC_FILERELATION (FID,PARENTID,LEVEL,TYPE) VALUES ('" +
                    fId + "','" + pid + "','" + level + "','" + type + "')");
            }

            sql_bath.append(
                ";INSERT INTO DOC_FILERELATION (FID,PARENTID,LEVEL,TYPE) VALUES ('" +
                fId + "','" + parentId + "','" + objList.size() + "','" + type +
                "')");
            this.trimmer.executeBatch(sql_bath.toString());
        }
    }

    /**
     * 重新设置层级关系表
     * @param parentId 父节点ID
     * @param fId 文件夹ID
     * @param type 类型
     */
    public void resetRelation(String parentId, String fId, String type) {
        /*删除当前文件夹关系*/
        this.deleteRelation(fId);
        /*插入当前文件夹与目标文件夹关系*/
        this.insertRelation(parentId, fId, "FOLDER");

        String sql = "SELECT PK_DOC_HGBGODKQXB FROM DOC_HGBGODKQXB WHERE COL_PARENTFOLDERID = " +
            fId;

        List childrenList = this.trimmer.searchMultiData(sql);

        for (int i = 0; i < childrenList.size(); i++) {
            Map m = (Map) childrenList.get(i);
            String id = (String) m.get("PK_DOC_HGBGODKQXB");

            resetRelation(fId, id, type);
        }
    }

    /**
     * 记录事件
     * @param fId
     * @param userid
     * @param username
     * @param content
     * @param type
     * @param version
     */
    public void insertEvent(String fId, String userid, String username,
        String content, String type, String version) {
        SimpleDateFormat dateformat2 = new SimpleDateFormat(
                "yyyy年MM月dd日 HH:mm:ss");
        String date = dateformat2.format(new Date());
        String sql_insert = "INSERT INTO DOC_EVENTS (FID,USERID,USERNAME,CTIME,CONTENT,TYPE,VERSION) VALUES ('" +
            fId + "','" + userid + "','" + username + "','" + date + "','" +
            content + "','" + type + "','" + version + "')";
        this.trimmer.execute(sql_insert);
    }

    /**
     *
     * @param fId
     * @return
     */
    public Map getPath(String fId) {
        Map m = new HashMap();

        // 生成全路径
        if ("-1".equals(fId)) {
            m.put("fullpathId", "");
            m.put("fullpath", "");
        } else {
            Map pfolder = getFolder(fId);
            StringBuilder path = new StringBuilder();
            StringBuilder ids = new StringBuilder();

            while ((pfolder != null) &&
                    !"-1".equals((String) pfolder.get("PK_DOC_HGBGODKQXB"))) {
                path.insert(0, (String) pfolder.get("COL_FOLDERNAME"));
                path.insert(0, '/');
                ids.insert(0, (String) pfolder.get("PK_DOC_HGBGODKQXB"));
                ids.insert(0, '/');
                pfolder = getFolder((String) pfolder.get("COL_PARENTFOLDERID"));
            }

            m.put("fullpathId", ids.toString());
            m.put("fullpath", path.toString());
        }

        return m;
    }

    /**
     * 删除层级关系表中相应的记录
     * @param fId 文件表id
     */
    public void deleteRelation(String fId) {
        String sql = "delete  from DOC_FILERELATION where FID = '" + fId + "'";
        this.trimmer.execute(sql);
    }

    /**
     * 删除事件表中相应的记录
     * @param fId 文件表id
     */
    public void deleteEvents(String fId) {
        String sql = "delete  from DOC_EVENTS where FID = '" + fId + "'";
        this.trimmer.execute(sql);
    }

    /*
     * 将附件表中某一文件对应的当前记录与历史记录的fk_annex_id与annex_object调换 @param fk_annx_id_file
     * 文件信息表的id @param fk_annx_id_his 历史记录表的id
     */
    public void swift(String fk_annx_id_file, String fk_annx_id_his) {
        // 获取新增历史记录对应的附件记录id
        String sql_id = "select xsystem_annex_id from xsystem_annex where fk_annex_id = '" +
            fk_annx_id_his + "' and annex_object = 'DOC_FILEHISTORY' ";
        Map m = this.trimmer.searchSingleData(sql_id);
        String id = (String) m.get("xsystem_annex_id");
        String sql1 = "update xsystem_annex set fk_annex_id = '" +
            fk_annx_id_his +
            "' , annex_object = 'DOC_FILEHISTORY' where fk_annex_id = '" +
            fk_annx_id_file + "' and annex_object = 'DOC_BVFABRYJMQ' ";
        String sql2 = "update xsystem_annex set fk_annex_id = '" +
            fk_annx_id_file +
            "' , annex_object = 'DOC_BVFABRYJMQ' where xsystem_annex_id = '" +
            id + "'";
        String sql = sql1 + ";" + sql2;
        this.trimmer.executeBatch(sql);
    }

    /**
     * 更新记录的版本号及文件大小
     *
     * @param id
     * @param version
     */
    public void updateVersion(String id, String version, String fileSize) {
        String sql = "UPDATE DOC_BVFABRYJMQ SET COL_VERSION =  '" + version +
            "',COL_FILESIZE = '" + fileSize + "' WHERE PK_DOC_BVFABRYJMQ = '" +
            id + "'";
        this.trimmer.execute(sql);
    }

    /**
     * 查询授权对象的id,以逗号分隔
     * @param sql sql语句
     * @return
     */
    public String getPurviewObjIds(String sql) {
        StringBuffer ids = new StringBuffer("");
        List<Map> result = this.trimmer.searchMultiData(sql);

        for (int i = 0; i < result.size(); i++) {
            String objId = (String) result.get(i).get("OBJ_ID");

            if (i == 0) {
                ids.append("'" + objId + "'");
            } else {
                ids.append(",'" + objId + "'");
            }
        }

        return ids.toString();
    }
}
